package dbexp.connection;

import static dbexp.model.db.DbOT.FUNCTION;
import static dbexp.model.db.DbOT.INDEX;
import static dbexp.model.db.DbOT.PACKAGE;
import static dbexp.model.db.DbOT.PACKAGEBODY;
import static dbexp.model.db.DbOT.PROCEDURE;
import static dbexp.model.db.DbOT.ROLE;
import static dbexp.model.db.DbOT.SEQUENCE;
import static dbexp.model.db.DbOT.SYNONYM;
import static dbexp.model.db.DbOT.TABLE;
import static dbexp.model.db.DbOT.TRIGGER;
import static dbexp.model.db.DbOT.VIEW;
import static dbexp.utils.Error.sql;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import dbexp.connection.model.Client;
import dbexp.connection.model.ConnectionInfo;
import dbexp.model.Column;
import dbexp.model.DbObject;
import dbexp.model.Error;
import dbexp.model.Index;
import dbexp.model.Schema;
import dbexp.model.Sequence;
import dbexp.model.SourceType;
import dbexp.model.Synonym;
import dbexp.model.Table;
import dbexp.model.TableData;
import dbexp.model.Trigger;
import dbexp.model.User;
import dbexp.model.View;
import dbexp.model.db.DbUser;
import dbexp.utils.DbProperties;
import dbexp.utils.StringUtil;
import dbexp.utils.Tool;

/**
 * @author Michał Skrzyński
 * @email michal_skrzynski@o2.pl
 */
public class DbConnection {
	private static DbConnection instance = new DbConnection();
	private static ConnectionInfo ci = null;
	private static boolean isAutoClose = DbProperties.getInstance()
			.defAutoClose();
	private final PoolManager dbm = PoolManager.getInstance();
	protected Connection con = null;
	protected DatabaseMetaData dmd = null;

	protected DbConnection() {
	}

	protected DbConnection(final ConnectionInfo ci) throws PoolManagerException,
			DbStateException, DbAccessException, SQLException {
		this.ci = ci;
		con = dbm.getConnection(ci);
		dmd = con.getMetaData();
		instance = this;

	}
	protected DbConnection(final ConnectionInfo ci, Connection con) throws PoolManagerException,
	DbStateException, DbAccessException, SQLException {
		System.err.println("Creating a new Connection..."+this);
		this.ci = ci;
		this.con = con;
		dmd = con.getMetaData();
		instance = this;
		
	}
	public void close() throws SQLException{
		if(con != null){
			System.err.println("Destroing..."+this);
			con.close();
		}
		ConnectionPool.releaseConnection(ci, this);
	}
	public boolean isClosed(){
		boolean out = true;
		if(con != null){
			try {
				out = con.isClosed();
			}
			catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return out;
	}
	public static DbConnection getInstance() {
		return instance;
	}

	public static DbConnection getIFor(final DbUser u) {
		ci = new Client(u);
		return instance;
	}

	public String infoProp() {
		if (ci != null) {
			return ci.toString();
		}
		return "";
	}

	public void setAutoClose(final boolean autoClose) {
		isAutoClose = autoClose;
	}

	public boolean isAutoClose() {
		return isAutoClose;
	}

	public DbUser getNewUser(final DbUser u) throws PoolManagerException,
			DbStateException, DbAccessException, SQLException {
		ci = new Client(u);
		if (dbm.getConnection(ci) != null) {
			return loadPrivs(u);
		}
		return null;
	}

	public boolean destroyConnection(final DbUser u) {
		return dbm.closeCurrentConnection(ci);
	}

	public void cC(final Object... o) throws DbStateException {
		Connection con = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		if (o != null) {
			for (final Object element : o) {
				if ((element != null) && (element instanceof Connection)) {
					con = (Connection) element;
				} else if ((element != null) && (element instanceof ResultSet)) {
					rs = (ResultSet) element;
					if (rs != null) {
						cC(rs);
					}
				} else if ((element != null) && (element instanceof PreparedStatement)) {
					ps = (PreparedStatement) element;
					if (ps != null) {
						cC(ps);
					}
				}
			}
			if ((con != null) && isAutoClose) {
				cC(con);
			}
		}
	}

	public DbUser loadPrivs(final DbUser u) throws SQLException, PoolManagerException,
			DbAccessException, DbStateException {
		final DbUser out = u;
		String s = "SELECT * FROM SESSION_ROLES";
		final PreparedStatement rolePs = prepareStatement(s);
		final ResultSet roleRs = rolePs.executeQuery();
		while (roleRs.next()) {
			out.addRole(roleRs.getString("ROLE"));
		}
		s = "SELECT * FROM SESSION_PRIVS";
		final PreparedStatement prPs = prepareStatement(s);
		final ResultSet prRs = prPs.executeQuery();
		while (prRs.next()) {
			out.addSystemPrivs(prRs.getString("PRIVILEGE"));
		}
		s = "SELECT * FROM USER_USERS";
		final PreparedStatement userPs = prepareStatement(s);
		final ResultSet userRs = userPs.executeQuery();
		while (userRs.next()) {
			out.setStatus(userRs.getString("ACCOUNT_STATUS"));
			out.setLockDate(userRs.getString("LOCK_DATE"));
			out.setExpiryDate(userRs.getString("EXPIRY_DATE"));
			out.setDefaultTableSpace(userRs.getString("DEFAULT_TABLESPACE"));
			out
					.setTemporaryTableSpace(userRs
							.getString("TEMPORARY_TABLESPACE"));
			out.setCreated(userRs.getString("CREATED"));
		}

		cC(rolePs, roleRs, prPs, prRs, userPs, userRs );
		return out;
	}

	public User loadPrivs(final User u) throws SQLException, PoolManagerException,
			DbAccessException, DbStateException {
		final User out = u;
		String s = "SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE=?";
		final PreparedStatement rolePs = prepareStatement(s);
		rolePs.setString(1, u.getName());
		final ResultSet roleRs = rolePs.executeQuery();
		while (roleRs.next()) {
			out.addRole(roleRs.getString("GRANTED_ROLE"));
		}
		s = "SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE=?";
		final PreparedStatement prPs = prepareStatement(s);
		prPs.setString(1, u.getName());
		final ResultSet prRs = prPs.executeQuery();
		while (prRs.next()) {
			out.addSystemPrivs(prRs.getString("PRIVILEGE"));
		}
		cC(rolePs, roleRs, prPs, prRs );
		return out;
	}

	private void cC(final Connection con) throws DbStateException {
		try {
			if ((con != null) && isAutoClose) {
				con.close();
			}
		} catch (final SQLException sqle) {
			sql.throwDbStateException(sqle);
		}
	}

	private void cC(final ResultSet rs) throws DbStateException {
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (final SQLException sqle) {
			sql.throwDbStateException(sqle);
		}
	}

	private void cC(final Statement ps) throws DbStateException {
		try {
			if (ps != null) {
				ps.close();
			}
		} catch (final SQLException sqle) {
			sql.throwDbStateException(sqle);
		}
	}

	public Connection getConnection() throws DbAccessException,
			DbStateException, PoolManagerException {
		return dbm.getConnection(ci);
	}

	public ResultSet getPrimaryKeys(final String catalog, final String schema, final String table)
			throws PoolManagerException, DbAccessException, DbStateException,
			DbQueryException, SQLException {
		return dbm.getConnection(ci).getMetaData().getPrimaryKeys(catalog,
				schema, table);
	}

	public List<Schema> getSchemas(final String catalog) throws PoolManagerException,
			DbAccessException, DbStateException, DbQueryException, SQLException {
		final List<Schema> list = new ArrayList<Schema>();
		ResultSet rs = null;
		try {
			rs = getMetaData().getSchemas();
			while (rs.next()) {
				final Schema schema = new Schema(rs.getString("TABLE_SCHEM"));
				list.add(schema);
			}
			return list;
		} catch (final PoolManagerException dbme) {
			throw new DbAccessException("ERROR:" + dbme.getMessage());
		} catch (final SQLException sqle) {
			throw new DbQueryException("ERROR CODE:" + sqle.getErrorCode()
					+ ",MESSAGE:" + sqle.getMessage());
		} finally {
			cC(rs);
		}
	}

	public List<String> getTableTypes() throws PoolManagerException,
			DbAccessException, DbStateException, DbQueryException, SQLException {
		final List<String> list = new ArrayList<String>();
		ResultSet rs = null;
		try {
			rs = getMetaData().getTableTypes();
			while (rs.next()) {
				list.add(rs.getString("TABLE_TYPE"));
			}
			return list;
		} catch (final PoolManagerException dbme) {
			System.err.println("ERROR:" + dbme.getMessage());
		} catch (final SQLException sqle) {
			sql.throwDbStateException(sqle);
		} finally {
			cC(rs);
		}
		return new ArrayList<String>();
	}

	public List<Column> getColumns(final String tableName, final String schema)
			throws PoolManagerException, DbAccessException, DbStateException,
			DbQueryException, SQLException {
		final List<Column> list = new ArrayList<Column>();
		ResultSet rs = null;
		try {
			rs = getMetaData().getColumns(null, schema, tableName, null);
			while (rs.next()) {
				final Column column = new Column(rs.getString("COLUMN_NAME"), schema,
						tableName);
				column.setDataType(rs.getString("TYPE_NAME"));
				column.setSqlType(rs.getInt("DATA_TYPE")); // int => SQL type
															// from
															// java.sql.Types
				column.setDataSize(rs.getLong("COLUMN_SIZE"));
				column.setDataScale(rs.getString("DECIMAL_DIGITS"));
				column.setNullable(rs.getString("IS_NULLABLE")
						.equalsIgnoreCase("YES"));
				column.setDataDefault(rs.getString("COLUMN_DEF"));
				list.add(column);
			}
			return list;
		} catch (final PoolManagerException dbme) {
			System.err.println("ERROR:" + dbme.getMessage());
		} catch (final SQLException sqle) {
			sqle.printStackTrace();
			sql.throwDbStateException(sqle);
		} finally {
			cC(rs);
		}
		return new ArrayList<Column>();
	}

	public Index getIndex(final String indexName, final String schema)
			throws PoolManagerException, DbAccessException, DbStateException,
			DbQueryException, SQLException {
		final Index out = new Index(indexName, schema);
		ResultSet rs = null;
		final String query = "SELECT I.OWNER,I.INDEX_NAME,IC.INDEX_OWNER,I.TABLE_OWNER,I.TABLESPACE_NAME,"
				+ "I.INDEX_TYPE,IC.TABLE_NAME,IC.COLUMN_NAME,I.UNIQUENESS, IC.COLUMN_POSITION,IC.COLUMN_LENGTH,ATC.DATA_TYPE "
				+ "FROM ALL_INDEXES I,ALL_IND_COLUMNS IC,ALL_TAB_COLUMNS ATC "
				+ "WHERE I.INDEX_NAME=? "
				+ "AND I.INDEX_NAME=IC.INDEX_NAME "
				+ "AND I.OWNER=?"
				+ "AND I.OWNER=ATC.OWNER "
				+ "AND ATC.TABLE_NAME=IC.TABLE_NAME "
				+ "AND ATC.COLUMN_NAME=IC.COLUMN_NAME ";
		PreparedStatement ps = null;
		try {
			ps = prepareStatement(query);
			ps.setString(1, indexName);
			ps.setString(2, schema);
			rs = ps.executeQuery();
			while (rs.next()) {
				final String tableName = rs.getString("TABLE_NAME");
				final String schemaName = rs.getString("TABLE_OWNER");
				final String indexType = rs.getString("INDEX_TYPE");
				if (indexType.indexOf("BITMAP") != -1) {
					out.setBitmap(true);
				}
				if (indexType.indexOf("REV") != -1) {
					out.setReverse(true);
				}
				if (rs.getString("UNIQUENESS").indexOf("UNIQUE") != -1) {
					out.setUnique(true);
				}
				out.setOnTable(tableName);
				out.setOnSchema(schemaName);
				out.setTablespace(rs.getString("TABLESPACE_NAME"));
				final Column column = new Column(rs.getString("COLUMN_NAME"), schema,
						tableName);
				column.set("COLUN_POSITION", rs.getInt("COLUMN_POSITION"));
				column.setDataType(rs.getString("DATA_TYPE"));
				column.setDataSize(rs.getLong("COLUMN_LENGTH"));
				out.addColumn(column);
			}
			return out;
		} catch (final PoolManagerException dbme) {
			System.err.println("ERROR:" + dbme.getMessage());
		} catch (final SQLException sqle) {
			sqle.printStackTrace();
			sql.throwDbStateException(sqle);
		} finally {
			cC(rs, ps);
		}
		return new Index("NOTFOUND");
	}

	public ArrayList<Error> checkErrors(final DbObject o) throws PoolManagerException,
			DbAccessException, DbStateException, DbQueryException, SQLException {
		final ArrayList<Error> errors = new ArrayList<Error>();
		ResultSet rs = null;
		final String query = "SELECT OWNER,NAME,TYPE,SEQUENCE,LINE,POSITION,TEXT FROM ALL_ERRORS WHERE OWNER=UPPER(?) AND UPPER(NAME)=UPPER(?)";
		PreparedStatement ps = null;
		try {
			ps = prepareStatement(query);
			ps.setString(1, o.getSchema());
			ps.setString(2, o.getName());
			rs = ps.executeQuery();
			int nr = 0;
			while (rs.next()) {
				final Error error = new Error();
				error.setSchema(rs.getString("OWNER"));
				error.setName(rs.getString("NAME"));
				error.setTyp(ERROR);
				error.setSequence(rs.getInt("SEQUENCE"));
				error.setLine(rs.getInt("LINE"));
				error.setPosition(rs.getInt("POSITION"));
				error.setText(rs.getString("TEXT"));
				errors.add(nr++, error);
			}
			return errors;
		} catch (final PoolManagerException dbme) {
			System.err.println("ERROR:" + dbme.getMessage());
		} catch (final SQLException sqle) {
			sqle.printStackTrace();
			sql.throwDbStateException(sqle);
		} finally {
			cC(rs, ps);
		}
		return new ArrayList<Error>();
	}

	public View getView(final String name, final String schema) throws PoolManagerException,
			DbAccessException, DbStateException, DbQueryException, SQLException {
		final View view = new View(name, schema);
		ResultSet rs = null;
		final String query = "SELECT OWNER,VIEW_NAME,TEXT_LENGTH,TEXT FROM ALL_VIEWS WHERE OWNER=UPPER(?) AND VIEW_NAME=?";
		PreparedStatement ps = null;
		try {
			ps = prepareStatement(query);
			ps.setString(1, schema);
			ps.setString(2, name);
			rs = ps.executeQuery();
			if (rs.next()) {
				String text = rs.getString("TEXT").replaceAll(" FORCE", "")
						.replaceAll("", "");
				if (text.indexOf("WITH") != -1) {
					text = text.substring(0, text.indexOf("WITH"));
				}
				view.set("length", rs.getString("TEXT_LENGTH"));
				view.setContent(text);
				view.setErrors(checkErrors(view));
				return view;
			}
		} catch (final PoolManagerException dbme) {
			System.err.println("ERROR:" + dbme.getMessage());
		} catch (final SQLException sqle) {
			sqle.printStackTrace();
			sql.throwDbStateException(sqle);
		} finally {
			cC(rs, ps);
		}
		return new View("NOTFOUND");
	}

	public Table getTable(final String tableName, final String schema)
			throws PoolManagerException, DbAccessException, DbStateException,
			DbQueryException, SQLException {
		final Table t = new Table(tableName, schema);
		ResultSet rs = null;
		ResultSet pk = null;
		final String query = "select * from ALL_TABLES WHERE OWNER=UPPER(?) AND TABLE_NAME=?";
		PreparedStatement ps = null;
		try {
			ps = prepareStatement(query);
			ps.setString(1, schema);
			ps.setString(2, tableName);
			rs = ps.executeQuery();
			if (rs.next()) {
				t.setTableSpace(rs.getString("TABLESPACE_NAME"));
			} else {
				return new Table("NOTFOUND");
			}
			t.setColumns(getColumns(tableName, schema));
			pk = getPrimaryKeys(null, schema, tableName);
			String pkColumn = "";
			String pkName = null;
			while (pk.next()) {
				pkColumn = pk.getString("COLUMN_NAME");
				pkName = pk.getString("PK_NAME");
				if (pkName != null) {
					for (int i = 0; i < t.getColumns().size(); i++) {
						if (t.getColumns().get(i).getName().equals(pkColumn)) {
							t.getColumns().get(i).setPK(true);
							break;
						}
					}
				}
			}
			return t;
		} catch (final PoolManagerException dbme) {
			System.err.println("ERROR:" + dbme.getMessage());
		} catch (final SQLException sqle) {
			sqle.printStackTrace();
			sql.throwDbStateException(sqle);
		} finally {
			cC(rs, ps, pk);
		}
		return new Table("NOTFOUND");
	} // TODO zrobić

	public Trigger getTrigger(final String name, final String schema)
			throws PoolManagerException, DbAccessException, DbStateException,
			DbQueryException, SQLException {
		final Trigger trig = Trigger.instantOf(getSource(new Trigger(name, schema)));
		ResultSet rs = null;
		final String query = "SELECT * FROM ALL_TRIGGERS WHERE OWNER=UPPER(?) AND TRIGGER_NAME=? AND ROWNUM=1";
		PreparedStatement ps = null;
		try {
			ps = prepareStatement(query);
			ps.setString(1, schema);
			ps.setString(2, name);
			rs = ps.executeQuery();
			if (rs.next()) {
				final String type = rs.getString("TRIGGER_TYPE");
				trig.set("type", type);// BEFORE (STATEMENT|EACH ROW)|AFTER
										// (STATEMENT|EACH ROW)
				if (type.indexOf("EACH ROW") != -1) {
					trig.setForEachRow(true);
				}
				if (type.startsWith("ALTER")) {
					trig.setWhen(Trigger.AFTER);
				} else {
					trig.setWhen(Trigger.BEFORE);
				}
				final String event = rs.getString("TRIGGERING_EVENT");
				if (event.indexOf("INSERT") != -1) {
					trig.setInsert(true);
				} else if (event.indexOf("DELETE") != -1) {
					trig.setDelete(true);
				} else if (event.indexOf("UPDATE") != -1) {
					trig.setUpdate(true);
				}
				trig.set("event", event);// INSERT OR UPDATE OR DELETE
				trig.set("table_owner", rs.getString("TABLE_OWNER"));
				trig.set("base_object_type", rs.getString("BASE_OBJECT_TYPE"));// TABLE
				trig.set("table_name", rs.getString("TABLE_NAME"));
				trig.set("ref_names", rs.getString("REFERENCING_NAMES"));
				trig.set("status", rs.getString("STATUS"));
			}
			return trig;
		} catch (final PoolManagerException dbme) {
			System.err.println("ERROR:" + dbme.getMessage());
		} catch (final SQLException sqle) {
			sqle.printStackTrace();
			sql.throwDbStateException(sqle);
		} finally {
			cC(rs, ps);
		}
		return new Trigger("NOTFOUND");
	}

//	public User getUser(final String name) throws PoolManagerException,
//			DbAccessException, DbStateException, DbQueryException, SQLException {
//		return new User(name);
//	}
	public User getUser(final String name) throws PoolManagerException,
			DbAccessException, DbStateException, DbQueryException, SQLException
	{
		final User out = new User(name);
		ResultSet rs = null;
		final String query = "SELECT * FROM DBA_USERS WHERE USERNAME=? AND ROWNUM=1";
		PreparedStatement ps = null;
		try {
			ps = prepareStatement(query);
			ps.setString(1, name);
			rs = ps.executeQuery();
			if (rs.next()) {
				out.setStatus(rs.getString("ACCOUNT_STATUS"));
				out.setProfile(rs.getString("PROFILE"));
				out.setCreated(rs.getString("CREATED"));
				out.setDefaultTableSpace(rs.getString("DEFAULT_TABLESPACE"));
				out.setTemporaryTableSpace(rs.getString("TEMPORARY_TABLESPACE"));
				loadPrivs(out);
			}
			return out;
		}
		catch (final PoolManagerException dbme) {
			System.err.println("ERROR:" + dbme.getMessage());
		}
		catch (final SQLException sqle) {
			sqle.printStackTrace();
			sql.throwDbStateException(sqle);
		}
		finally {
			cC(rs, ps);
		}
		return new User("NOTFOUND");
	}

	/*****************************************************************************************************************************/
	public Sequence getSequence(final String seqName, final String schema)
			throws PoolManagerException, DbAccessException, DbStateException,
			DbQueryException, SQLException {
		ResultSet rs = null;
		String query = "SELECT * FROM ALL_SEQUENCES";
		if ((schema != null) || (seqName != null)) {
			query += " WHERE ";
			if (schema != null) {
				query += " SEQUENCE_OWNER=? ";
			}
			if (seqName != null) {
				if (schema != null) {
					query += " AND ";
				}
				query += " SEQUENCE_NAME=? ";
			}
		}
		PreparedStatement ps = null;
		try {
			int i = 1;
			ps = prepareStatement(query);
			if (schema != null) {
				ps.setString(i++, schema.trim());
			}
			if (seqName != null) {
				ps.setString(i++, seqName.trim());
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				final Sequence ob = new Sequence(rs.getString("SEQUENCE_NAME"));
				ob.setMinValue(rs.getString("MIN_VALUE"));
				ob.setMaxValue(rs.getString("MAX_VALUE"));// 1.0E28
				ob.setIncrementBy(rs.getInt("INCREMENT_BY")); // o ile
				ob.setCycleFlag(rs.getString("CYCLE_FLAG").equals("Y")); // N/Y
				ob.setOrderFlag(rs.getString("ORDER_FLAG").equals("Y")); // Y/N
				ob.setCacheSize(rs.getInt("CACHE_SIZE")); // 20
				ob.setLastNumber(rs.getInt("LAST_NUMBER")); // 41
				ob.setSchema(rs.getString("SEQUENCE_OWNER"));
				return (ob);
			}
		} catch (final PoolManagerException dbme) {
			System.err.println("ERROR:" + dbme.getMessage());
		} catch (final SQLException sqle) {
			sqle.printStackTrace();
			sql.throwDbStateException(sqle);
		} finally {
			cC(rs, ps);
		}
		return new Sequence("NOTFOUND");
	}

	public Synonym getSynonym(final String seqName, final String schema)
			throws PoolManagerException, DbAccessException, DbStateException,
			DbQueryException, SQLException {
		ResultSet rs = null;
		String query = "SELECT S.*,O.OBJECT_TYPE FROM ALL_SYNONYMS S, ALL_OBJECTS O WHERE S.TABLE_NAME=O.OBJECT_NAME";
		if ((schema != null) || (seqName != null)) {
			if (schema != null) {
				query += " AND S.OWNER=? ";
			}
			if (seqName != null) {
				if (schema != null) {
					query += " AND ";
				}
				query += " SYNONYM_NAME=? ";
			}
		}
		PreparedStatement ps = null;
		try {
			int i = 1;
			ps = prepareStatement(query);
			if (schema != null) {
				ps.setString(i++, schema.trim());
			}
			if (seqName != null) {
				ps.setString(i++, seqName.trim());
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				final Synonym ob = new Synonym(rs.getString("SYNONYM_NAME"), rs
						.getString("OWNER"));
				ob.setTableOwner(rs.getString("TABLE_OWNER"));
				ob.setTableName(rs.getString("TABLE_NAME"));
				ob.setObjectType(rs.getString("OBJECT_TYPE"));
				ob.setDbLink(rs.getString("DB_LINK"));
				return (ob);
			}
		} catch (final PoolManagerException dbme) {
			System.err.println("ERROR:" + dbme.getMessage());
		} catch (final SQLException sqle) {
			sqle.printStackTrace();
			sql.throwDbStateException(sqle);
		} finally {
			cC(rs, ps);
		}
		return new Synonym("NOTFOUND");
	}

	/*****************************************************************************************************************************/
//	public List<Table> getTables(final String schema, final String tableName)
//			throws PoolManagerException, DbAccessException, DbStateException,
//			DbQueryException, SQLException {
//		final List<Table> list = new ArrayList<Table>();
//		ResultSet rs = null;
//		try {
//			rs = dmd.getTables(null, schema, tableName, new String[]{"TABLE"});
//			while (rs.next()) {
//				final String name = rs.getString("TABLE_NAME");
//				if (containsExtraName(name)) {
//					continue;
//				}
//				final Table table = new Table(name, rs.getString("TABLE_SCHEM"));
//
//				table.setTyp(TABLE);
//				list.add(table);
//			}
//			return list;
//		} catch (final SQLException sqle) {
//			sqle.printStackTrace();
//			sql.throwDbStateException(sqle);
//		} finally {
//			cC(rs);
//		}
//		return new ArrayList<Table>();
//	}
	
	public List<Table> getTables(final String schema, final String tableName)
			throws PoolManagerException, DbAccessException, DbStateException,
			DbQueryException, SQLException
	{
		final List<Table> list = new ArrayList<Table>();
		ResultSet rs = null;
		String query = "SELECT OWNER,TABLE_NAME,TABLESPACE_NAME,DEGREE,TABLE_LOCK,TEMPORARY FROM ALL_ALL_TABLES WHERE OWNER=UPPER(?)";
		if (tableName != null) {
			query += " AND TABLE_NAME=?";
		}
		PreparedStatement ps = null;
		try {
			ps = prepareStatement(query);
			ps.setString(1, schema);
			if (tableName != null) {
				ps.setString(2, tableName);
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				final String name = rs.getString("TABLE_NAME");
				if (containsExtraName(name)) {
					continue;
				}
				final Table table = new Table(name, rs.getString("OWNER"));
				
				table.setTableSpace(rs.getString("TABLESPACE_NAME"));
				table.setDegree(rs.getString("DEGREE"));
				table.setTableLock(rs.getString("TABLE_LOCK"));
				table.setTemporary(rs.getString("TEMPORARY"));
				table.setTyp(TABLE);
				list.add(table);
			}
			return list;
		}
		catch (final PoolManagerException dbme) {
			System.err.println("ERROR:" + dbme.getMessage());
		}
		catch (final SQLException sqle) {
			sqle.printStackTrace();
			sql.throwDbStateException(sqle);
		}
		finally {
			cC(rs, ps);
		}
		return new ArrayList<Table>();
	}

	public List<DbObject> getTablespaces() throws DbAccessException,
			DbStateException {
		final List<DbObject> list = new ArrayList<DbObject>();
		ResultSet rs = null;
		final String query = "SELECT TABLESPACE_NAME FROM USER_TABLESPACES";
		PreparedStatement ps = null;
		try {
			ps = prepareStatement(query);
			rs = ps.executeQuery();
			while (rs.next()) {
				final String name = rs.getString("TABLESPACE_NAME");
				list.add(new DbObject(name));
			}
			return list;
		} catch (final PoolManagerException dbme) {
			System.err.println("ERROR:" + dbme.getMessage());
		} catch (final SQLException sqle) {
			sqle.printStackTrace();
			sql.throwDbStateException(sqle);
		} finally {
			cC(rs, ps);
		}
		return new ArrayList<DbObject>();
	}

	public List<DbObject> getProfile() throws DbAccessException,
			DbStateException {
		final List<DbObject> list = new ArrayList<DbObject>();
		ResultSet rs = null;
		final String query = "SELECT PROFILE FROM DBA_PROFILES GROUP BY PROFILE";
		PreparedStatement ps = null;
		try {
			ps = prepareStatement(query);
			rs = ps.executeQuery();
			while (rs.next()) {
				final String name = rs.getString("PROFILE");
				list.add(new DbObject(name));
			}
			return list;
		} catch (final PoolManagerException dbme) {
			System.err.println("ERROR:" + dbme.getMessage());
		} catch (final SQLException sqle) {
			sqle.printStackTrace();
			sql.throwDbStateException(sqle);
		} finally {
			cC(rs, ps);
		}
		return new ArrayList<DbObject>();
	}

	public List<DbObject> getRoles(final DbObject o) throws DbAccessException,
			DbStateException {
		final List<DbObject> list = new ArrayList<DbObject>();
		ResultSet rs = null;
		final String query = "SELECT * FROM DBA_ROLES";
		PreparedStatement ps = null;
		try {
			ps = prepareStatement(query);
			rs = ps.executeQuery();
			while (rs.next()) {
				final String objectName = rs.getString("ROLE");
				if (containsExtraName(objectName)) {
					continue;
				}
				final DbObject fn = new DbObject(objectName);
				fn.setTyp(o.getType());
				fn.setIcons(o.getValidIcon(), o.getCategoriesIcon());
				fn.set("leaf", true);
				list.add(fn);
			}
			return list;
		} catch (final PoolManagerException dbme) {
			System.err.println("ERROR:" + dbme.getMessage());
		} catch (final SQLException sqle) {
			sqle.printStackTrace();
			sql.throwDbStateException(sqle);
		} finally {
			cC(rs, ps);
		}
		return new ArrayList<DbObject>();
	}

	public List<DbObject> getSystemPrivs(final DbObject o) throws DbAccessException,
			DbStateException {
		final List<DbObject> list = new ArrayList<DbObject>();
		ResultSet rs = null;
		final String query = "SELECT * FROM SYS.SYSTEM_PRIVILEGE_MAP";
		PreparedStatement ps = null;
		try {
			ps = prepareStatement(query);
			rs = ps.executeQuery();
			while (rs.next()) {
				final String objectName = rs.getString("NAME");
				if (containsExtraName(objectName)) {
					continue;
				}
				final DbObject fn = new DbObject(objectName);
				fn.setTyp(o.getType());
				fn.setIcons(o.getValidIcon(), o.getCategoriesIcon());
				fn.set("leaf", true);
				list.add(fn);
			}
			return list;
		} catch (final PoolManagerException dbme) {
			System.err.println("ERROR:" + dbme.getMessage());
		} catch (final SQLException sqle) {
			sqle.printStackTrace();
			sql.throwDbStateException(sqle);
		} finally {
			cC(rs, ps);
		}
		return new ArrayList<DbObject>();
	}

	public List<DbObject> getUsers(final DbObject o) throws DbAccessException,
			DbStateException {
		final List<DbObject> list = new ArrayList<DbObject>();
		ResultSet rs = null;
		final String query = "SELECT * FROM ALL_USERS";
		PreparedStatement ps = null;
		try {
			ps = prepareStatement(query);
			rs = ps.executeQuery(); // PROFILE,
									// RESOURCE_NAME,RESOURCE_TYPE,LIMIT
			while (rs.next()) {
				final String objectName = rs.getString("USERNAME");
				if (containsExtraName(objectName)) {
					continue;
				}
				final DbObject fn = new DbObject(objectName);
				fn.setTyp(o.getType());
				fn.setIcons(o.getValidIcon(), o.getCategoriesIcon());
				fn.set("leaf", true);
				list.add(fn);
			}
			return list;
		} catch (final PoolManagerException dbme) {
			System.err.println("ERROR:" + dbme.getMessage());
		} catch (final SQLException sqle) {
			sqle.printStackTrace();
			sql.throwDbStateException(sqle);
		} finally {
			cC(rs, ps);
		}
		return new ArrayList<DbObject>();
	}

	/*****************************************************************************************************************************/
	public List<DbObject> getObjects(final DbObject o) throws PoolManagerException,
			DbAccessException, DbStateException, DbQueryException, SQLException {
		final List<DbObject> list = new ArrayList<DbObject>();
		ResultSet rs = null;
		final String stringType = getObjectStringType(o.getType());
		String typ = "";
		final String schema = o.getSchema();
		if (stringType != null) {
			typ = "AND OBJECT_TYPE=" + stringType;
		}
		final String query = "SELECT OWNER,OBJECT_NAME,TIMESTAMP,STATUS FROM ALL_OBJECTS WHERE OWNER=UPPER(?) "
				+ typ;
		PreparedStatement ps = null;
		try {
			ps = prepareStatement(query);
			ps.setString(1, schema);
			rs = ps.executeQuery(); // TRIGGER,PROCEDURE,PACKAGE,PACKAGE
									// BODY,SEQUENCE,FUNCTION,TABLE,VIEW,SYNONYM,INDEX
			while (rs.next()) {
				final String objectName = rs.getString("OBJECT_NAME");
				if (containsExtraName(objectName)) {
					continue;
				}
				final DbObject fn = new DbObject(objectName, rs.getString("OWNER"));
				fn.setTyp(o.getType());
				fn.setIcons(o.getValidIcon(), o.getCategoriesIcon());
				fn.set("leaf", true);
				final boolean valid = rs.getString("STATUS")
						.equalsIgnoreCase("VALID");
				fn.setValid(valid);
				list.add(fn);
			}
			return list;
		} catch (final PoolManagerException dbme) {
			System.err.println("ERROR:" + dbme.getMessage());
		} catch (final SQLException sqle) {
			sqle.printStackTrace();
			sql.throwDbStateException(sqle);
		} finally {
			cC(rs, ps);
		}
		return new ArrayList<DbObject>();
	}

	public DbObject getObject(final DbObject o) throws PoolManagerException,
			DbAccessException, DbStateException, DbQueryException, SQLException {
		ResultSet rs = null;
		final String stringType = getObjectStringType(o.getType());
		String typ = "";
		final String schema = o.getSchema();
		final String name = o.getName();
		if (stringType != null) {
			typ = "AND OBJECT_TYPE=" + stringType;
		}
		String query = "SELECT OWNER,OBJECT_NAME,TIMESTAMP,STATUS FROM ALL_OBJECTS WHERE OWNER=UPPER(?) "
				+ typ;
		query += " AND OBJECT_NAME=?";
		PreparedStatement ps = null;
		try {
			ps = prepareStatement(query);
			ps.setString(1, schema);
			ps.setString(2, name);
			rs = ps.executeQuery(); // TRIGGER,PROCEDURE,PACKAGE,PACKAGE
									// BODY,SEQUENCE,FUNCTION,TABLE,VIEW,SYNONYM,INDEX
			if (rs.next()) {
				final String objectName = rs.getString("OBJECT_NAME");
				if (containsExtraName(objectName)) {
					return new DbObject("NOTFOUND");
				}
				final DbObject fn = new DbObject(objectName, rs.getString("OWNER"));
				fn.setTyp(o.getType());
				fn.setIcons(o.getValidIcon(), o.getCategoriesIcon());
				fn.set("leaf", true);
				final boolean valid = rs.getString("STATUS")
						.equalsIgnoreCase("VALID");
				fn.setValid(valid);
				return fn;
			}
		} catch (final PoolManagerException dbme) {
			System.err.println("ERROR:" + dbme.getMessage());
		} catch (final SQLException sqle) {
			sqle.printStackTrace();
			sql.throwDbStateException(sqle);
		} finally {
			cC(rs, ps);
		}
		return new DbObject("NOTFOUND");
	}

	public SourceType getSource(final DbObject so) throws PoolManagerException,
			DbAccessException, DbStateException, DbQueryException, SQLException {
		final SourceType source = new SourceType(so.getName(), so.getSchema());
		ResultSet rs = null;
		final String query = "SELECT TEXT FROM ALL_SOURCE WHERE OWNER=? AND TYPE=? AND NAME=?";
		PreparedStatement ps = null;
		try {
			final String typ = getObjectStringType(so.getType()).replaceAll("'", "");
			final String schema = so.getSchema();
			final String name = so.getName();

			ps = prepareStatement(query);
			ps.setString(1, schema.trim());
			ps.setString(2, typ);
			ps.setString(3, name.trim());
			rs = ps.executeQuery();
			System.out.println("" + rs.toString());
			final ArrayList<String> lines = new ArrayList<String>();
			final StringBuffer sb = new StringBuffer();
			boolean first = true;
			while (rs.next()) {
				String line = rs.getString("TEXT");
				if (first && (line.indexOf("wrapped") != -1)) {
					lines
							.add("Źródło nie może zostać wyświetlone, ponieważ jest ukryte/opakowane.");
					sb
							.append("Źródło nie może zostać wyświetlone, ponieważ jest ukryte/opakowane.");
					source.setWrapped(true);
					source.setLinies(sb);
					source.setLinies(lines);
					return source;
				} else {
					if (first && (line.trim().length() > 0)) {
						final int inxOf = indexOf(so, line);
						if (inxOf >= 0) {
							line = line.substring(inxOf);
							first = false;
						}
					}
					if (!first) {
						sb.append(line);
						lines.add(line);
					}
				}
			}
			final String generalOut = sb.toString();
			source.setLinies(lines);
			source.setLinies(new StringBuffer(generalOut));
			source.setErrors(checkErrors(so));
			return source;
		} catch (final PoolManagerException dbme) {
			System.err.println("ERROR:" + dbme.getMessage());
		} catch (final SQLException sqle) {
			sqle.printStackTrace();
			sql.throwDbStateException(sqle);
		} catch (final ArrayIndexOutOfBoundsException sqle) {
			System.err.println("ERROR CODE:" + sqle.getMessage());
			sqle.printStackTrace();
		} finally {
			cC(rs, ps);
		}
		return null;
	}

	public List<String> getTableColumns(final String table) throws SQLException,
			PoolManagerException, DbAccessException, DbStateException,
			DbQueryException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			final List<String> columns = new ArrayList<String>();
			stmt = prepareStatement("SELECT COLUMN_NAME"
					+ " FROM USER_TAB_COLUMNS" + " WHERE TABLE_NAME=?");
			stmt.setString(1, table.toUpperCase());
			rs = stmt.executeQuery();
			while (rs.next()) {
				columns.add(rs.getString(1));
			}
			return columns;
		} finally {
			cC(rs, stmt);
		}
	}

	public List<Column> getColumnsFromQuery(final String query) throws SQLException,
			PoolManagerException, DbAccessException, DbStateException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = prepareStatement(query);
			rs = stmt.executeQuery();
			int columnCount = -1;
			final ResultSetMetaData rsmd = rs.getMetaData();
			final List<Column> columns = new ArrayList<Column>();
			if (columnCount == -1) {
				columnCount = rsmd.getColumnCount();
				for (int i = 1; i <= columnCount; i++) {
					final Column column = new Column();
					column.set("columnClassName", rsmd.getColumnClassName(i));// java.lang.String
					column.set("columnDisplaySize", rsmd
							.getColumnDisplaySize(i));// 50
					column.setLabel(rsmd.getColumnLabel(i));// TOPIC
					column.setName(rsmd.getColumnName(i));// TOPIC
					column.set("columnType", rsmd.getColumnType(i));// 12
					column.setDataType(rsmd.getColumnTypeName(i));// VARCHAR2
					column.set("scale", rsmd.getScale(i));// 0
					column.setNullable(rsmd.isNullable(i) == 1);//
					columns.add(column);
				}
			}
			return columns;
		} finally {
			cC(rs, stmt);
		}
	}
	
	public String getDDL(final DbObject o) throws PoolManagerException, DbAccessException, DbStateException, SQLException{
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = prepareStatement("SELECT DBMS_METADATA.GET_DDL('"+getObjectStringType(o.getType())+"', '"+o.getName()+"', '"+o.getSchema()+"')AS DDL FROM DUAL");
			rs = stmt.executeQuery();
			if (rs.next()) {
				return rs.getString("DDL");
			}
		} finally {
			cC(rs, stmt);
		}
		return "";
	}
	
	public TableData getData(String query) throws SQLException,
			PoolManagerException, DbAccessException, DbStateException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			final TableData td = new TableData();
			final List<Column> columns = getColumnsFromQuery(query);
			td.setColumns(columns);
			if(StringUtil.count(query, "[*]")>1){
				query = query.replaceFirst("[*]", StringUtil.join(columns.toArray()));
			}
			stmt = prepareStatement(query);
			rs = stmt.executeQuery();

			while (rs.next()) {
				final List<Object> oneRow = new ArrayList<Object>();
				for (int i = 1; i <= columns.size(); i++) {
					oneRow.add(rs.getObject(i));
				}
				td.addData(oneRow);
			}
			return td;
		} finally {
			cC(rs, stmt);
		}
	}

	public long getCount(final String name) throws SQLException, PoolManagerException,
			DbAccessException, DbStateException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = prepareStatement("SELECT COUNT(*)AS ILE FROM " + name);
			rs = stmt.executeQuery();
			if (rs.next()) {
				return rs.getLong("ILE");
			}
		} finally {
			cC(rs, stmt);
		}
		return 0;
	}

	public List<String> getTableColumns(final String schema, final String table)
			throws SQLException, PoolManagerException, DbAccessException,
			DbStateException, DbQueryException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			final List<String> columns = new ArrayList<String>();
			stmt = prepareStatement("SELECT COLUMN_NAME"
					+ " FROM ALL_TAB_COLUMNS"
					+ " WHERE OWNER=? AND TABLE_NAME=?");
			stmt.setString(1, schema.toUpperCase());
			stmt.setString(2, table.toUpperCase());
			rs = stmt.executeQuery();
			while (rs.next()) {
				columns.add(rs.getString(1));
			}
			return columns;
		} finally {
			cC(rs, stmt);
		}
	}

	/**
	 * @param sql
	 * @return either (1) the row count for SQL Data Manipulation Language (DML)
	 *         statements or (2) 0 for SQL statements that return nothing
	 * @throws SQLException
	 * @throws PoolManagerException
	 * @throws DbAccessException
	 * @throws DbStateException
	 * @throws DbQueryException
	 */
	public int executeUpdate(final String sql) throws SQLException,
			PoolManagerException, DbAccessException, DbStateException {
		return prepareStatement(sql).executeUpdate();
	}

	public int operateObject(final DbObject o, final int operate) throws SQLException,
			PoolManagerException, DbAccessException, DbStateException,
			DbQueryException {
		int out = OK;
		String query = "";
		query = o.getQuery(operate);
		final String finalQuery = query.replaceAll("\n", " ");
		PreparedStatement ps = null;
		try {
			if (!o.isSource()) {
				final String[] qs = StringUtil.split(finalQuery, ";", Tool.trim());
				for (final String element : qs) {
					if (element.length() > 0) {
						ps = prepareStatement(element);
						out = ps.executeUpdate();
					}
				}
			} else {
				ps = prepareStatement(finalQuery);
				out = ps.executeUpdate();
			}
		} finally {
			cC(ps);
		}
		return out;
	}

	private DatabaseMetaData getMetaData() throws PoolManagerException,
			DbAccessException, DbStateException, DbQueryException, SQLException {
		return dbm.getConnection(ci).getMetaData();
	}

	public PreparedStatement prepareStatement(final String sql)
			throws PoolManagerException, DbAccessException, DbStateException,
			SQLException {
		System.out.println("Wykonuje zapytanie:" + sql);
		return dbm.getConnection(ci).prepareStatement(sql);
	}

	public String getExtraNameCharacters() throws PoolManagerException,
			DbAccessException, DbStateException, DbQueryException, SQLException {
		return dbm.getConnection(ci).getMetaData().getExtraNameCharacters();
	}

	/**
	 * @param t
	 *            DbOT
	 * @return reprezantacja znakowa typu dla zapytan, moze byc null
	 */
	public static String getObjectStringType(final int t) {
		final String typ = null;
		if (t == PACKAGE) {
			return "'PACKAGE'";
		} else if (t == PACKAGEBODY) {
			return "'PACKAGE BODY'";
		} else if (t == PROCEDURE) {
			return "'PROCEDURE'";
		} else if (t == FUNCTION) {
			return "'FUNCTION'";
		} else if (t == ROLE) {
			return "'ROLE'";
		} else if (t == SEQUENCE) {
			return "'SEQUENCE'";
		} else if (t == SYNONYM) {
			return "'SYNONYM'";
		} else if (t == TRIGGER) {
			return "'TRIGGER'";
		} else if (t == TABLE) {
			return "'TABLE'";
		} else if (t == VIEW) {
			return "'VIEW'";
		} else if (t == INDEX) {
			return "'INDEX'";
		}
		return typ;
	}

	/**
	 * @param t
	 *            DbOT
	 * @return reprezantacja znakowa typu dla zapytan, moze byc null
	 */
	public static int indexOf(final DbObject o, final String line) {
		final String checkL = line.toLowerCase();
		int out = 0;
		final int t = o.getType();
		final int paramc = checkL.indexOf("(");
		if (t == PACKAGE) {
			if (((out = checkL.indexOf(" is")) != -1)
					|| ((out = checkL.indexOf(" as")) != -1)) {
				return out;
			}
		} else if (t == PACKAGEBODY) {
			if (((out = checkL.indexOf(" is")) != -1)
					|| ((out = checkL.indexOf(" as")) != -1)) {
				return out;
			}
		} else if (t == PROCEDURE) {
			if (paramc != -1) {
				return paramc;
			}
			if (((out = checkL.indexOf(" is")) != -1)
					|| ((out = checkL.indexOf(" as")) != -1)) {
				return out;
			}
		} else if (t == FUNCTION) {
			if (paramc != -1) {
				return paramc;
			}
			if ((out = checkL.indexOf("return")) != -1) {
				return out;
			}
		} else if (t == ROLE) {
			return 0;
		} else if (t == SEQUENCE) {
			return 0;
		} else if (t == SYNONYM) {
			return 0;
		} else if (t == TRIGGER) {
			if (((out = checkL.indexOf("declare")) != -1)
					|| ((out = checkL.indexOf("begin")) != -1)) {
				return out;
			}
			return -1;
		} else if (t == TABLE) {
			return 0;
		} else if (t == VIEW) {
			return 0;
		}
		return out;
	}

	public boolean containsExtraName(final String checkingName) {
		try {
			final String[] eNC = getExtraNameCharacters().split("");
			for (final String element : eNC) {
				if ((element.trim().length() > 0)
						&& (checkingName.indexOf(element) != -1)) {
					return true;
				}
			}
		} catch (final PoolManagerException e) {
			e.printStackTrace();
		} catch (final DbAccessException e) {
			e.printStackTrace();
		} catch (final DbStateException e) {
			e.printStackTrace();
		} catch (final DbQueryException e) {
			e.printStackTrace();
		} catch (final SQLException e) {
			e.printStackTrace();
		}

		return false;
	}

	@SuppressWarnings("unused")
	private String getAccessTableQuery(final String query) {
		String out = "";
		if (ci != null) {
			if (((Client) ci).getUser().isAccessToDBA()) {
				out = "DBA_" + query;
			} else {
				out = "ALL_" + query;
			}
		}
		return out;
	}

	public Object parseInt(final String i, final String ifIsNull) {
		if ((ifIsNull != null) && ifIsNull.equals(i)) {
			return i;
		} else {
			try {
				final int x = Integer.parseInt(i);
				return new Integer(x);
			} catch (final NumberFormatException nfe) {
				if (ifIsNull != null) {
					return ifIsNull;
				}
			}
		}
		return null;
	}

	public static final int NONE = 0x000011;
	public static final int ALTER = 0x000012;
	public static final int SELECT = 0x000013;
	public static final int CREATE = 0x000014;
	public static final int DELETE = 0x000015;
	public static final int EXECUTE = 0x000016;
	public static final int EXIST = 0x000017;
	public static final int NOPERMISION = 0x000018;
	public static final int DROP_COLUMN = 0x000019;
	public static final int ADD_COLUMN = 0x000020;
	public static final int ALTER_COLUMN = 0x000021;
	public static final int INSERT_RECORD = 0x000022;
	public static final int UPDATE_RECORD = 0x000023;
	public static final int DELETE_RECORD = 0x000024;
	public static final int OK = 0x000000;
	public static final int ERROR = 0x000009;
	public static final String NF = "NOTFOUND";

}